﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace QLSV
{
    public partial class TongKet : Form
    {
        public TongKet()
        {
            InitializeComponent();
        }

        private SqlConnection Con;
        private DataTable Dt = new DataTable("TongKet");
        private DataTable DtTK = new DataTable("TongKet");
        private DataTable DtSV = new DataTable("SinhVien");
        private SqlDataAdapter Da = new SqlDataAdapter();
        private Boolean Kt;

        private void Connect()
        {
            String Cn = "Data Source=LENOVOY510\\SQLEXPRESS;Initial Catalog=QLSV;Integrated Security=True";
            try
            {
                Con = new SqlConnection(Cn);
                Con.Open();
            }
            catch (Exception Ex)
            {
                MessageBox.Show("Không Thể Kết Nối Đến Cơ Sở Dữ Liệu!", "Lỗi!", MessageBoxButtons.OK, MessageBoxIcon.Error);

            }
        }

        private void DisConnect()
        {
            Con.Close();
            Con.Dispose();
            Con = null;
        }

        private void GetData()
        {
            SqlCommand Com = new SqlCommand();
            Com.Connection = Con;
            Com.CommandType = CommandType.Text;

            Com.CommandText = "Select * from TongKet";
            Da.SelectCommand = Com;
            Da.Fill(DtTK);

            Com.CommandText = "Select * from SinhVien";
            Da.SelectCommand = Com;
            Da.Fill(DtSV);

            Com.CommandType = CommandType.Text;
            Com.CommandText = @"select  DiemTP.DMaSV as PMaSV,Sum(MonHoc.SoTC * DiemTP.DiemTB4) as TongTLM ,Sum(MonHoc.SoTC) as TongTC into Phu 
                                from DiemTP inner join MonHoc on DiemTP.DMaMH = MonHoc.MaMH 
                                group by DiemTP.DMaSV";
            Da.SelectCommand = Com;
            Da.Fill(Dt);

            Com.CommandType = CommandType.Text;
            Com.CommandText = @"Select MaSV as N'Mã Sinh Viên',
                                          Ho as N'Họ',
                                          Ten as N'Tên',
                                          convert(decimal(4,2),Phu.TongTLM/Phu.TongTC) as N'Điểm Trung Bình Tích Lũy',
                                          HocLuc as N'Học Lực',
	                                      HanhKiem as N'Hạnh Kiểm',
	                                      XepLoai as N'Xếp Loại'
                                from TongKet left join SinhVien
	                            on TongKet.XMaSV = SinhVien.MaSV
	                            full join Phu
	                            on TongKet.XMaSV = Phu.PMaSV";

            Da.SelectCommand = Com;
            Da.Fill(Dt);
            dgvtk.DataSource = Dt;

            dgvtk.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
            dgvtk.Columns[0].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
            dgvtk.Columns[2].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
            dgvtk.Columns[3].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
            dgvtk.Columns[4].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
            dgvtk.Columns[5].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
            dgvtk.Columns[6].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
        }

        private void TongKet_Load(object sender, EventArgs e)
        {
            Connect();
            GetData();
            Binding();
        }

        private void clickthoat_Click(object sender, EventArgs e)
        {
            SqlCommand Com = new SqlCommand();
            Com.Connection = Con;
            Com.CommandType = CommandType.Text;
            Com.CommandType = CommandType.Text;
            Com.CommandText = @"drop table Phu";
            Da.SelectCommand = Com;
            Da.Fill(Dt);
            Close();
            Dispose();
        }

        private void Binding()
        {
            txtmsv.DataBindings.Clear();
            txtmsv.DataBindings.Add("Text", dgvtk.DataSource, "Mã Sinh Viên");
            txth.DataBindings.Clear();
            txth.DataBindings.Add("Text", dgvtk.DataSource, "Họ");
            txtt.DataBindings.Clear();
            txtt.DataBindings.Add("Text", dgvtk.DataSource, "Tên");
            txtdtb.DataBindings.Clear();
            txtdtb.DataBindings.Add("Text", dgvtk.DataSource, "Điểm Trung Bình Tích Lũy");
            txthl.DataBindings.Clear();
            txthl.DataBindings.Add("Text", dgvtk.DataSource, "Học Lực");
            cbhk.DataBindings.Clear();
            cbhk.DataBindings.Add("Text", dgvtk.DataSource, "Hạnh Kiểm");
            txtxl.DataBindings.Clear();
            txtxl.DataBindings.Add("Text", dgvtk.DataSource, "Xếp Loại");
        }

        private bool KiemTra()
        {
            Kt = true;
            if (cbhk.Text == "")
            {
                MessageBox.Show("Bạn Chưa Nhập Hạnh Kiểm!", "Thông Báo!", MessageBoxButtons.OK);
                Kt = false;
            }

            return Kt;
        }

        private void clicksua_Click(object sender, EventArgs e)
        {
            KiemTra();
            if (Kt == true)
            {
                DataRow DtR = Dt.Select("[Mã Sinh Viên] = " + (txtmsv.Text))[0];
                DtR.BeginEdit();
                DtR["Hạnh Kiểm"] = cbhk.Text;
                DtR["Điểm Trung Bình Tích Lũy"] = txtdtb.Text;
                DtR.EndEdit();
                SqlCommand ComUpdate = new SqlCommand();
                ComUpdate.Connection = Con;
                ComUpdate.CommandType = CommandType.Text;
                ComUpdate.CommandText = @"Update TongKet set HanhKiem = @HanhKiem,
                                                             XepLoai = @XepLoai,
                                                             TBTL = @TBTL,
                                                             HocLuc = @HocLuc       
                                                             where XMaSV = @XMaSV";
                ComUpdate.Parameters.Add("@XMaSV", SqlDbType.NChar, 10, "Mã Sinh Viên");
                ComUpdate.Parameters.Add("@TBTL", SqlDbType.Float, 50, "Điểm Trung Bình Tích Lũy");

                float a = float.Parse(txtdtb.Text);
                if (a < 1)
                {
                    ComUpdate.Parameters.Add("@HocLuc", SqlDbType.NVarChar, 20).Value = "Yếu";
                }
                else if (a >= 1 && a < 2.5)
                {
                    ComUpdate.Parameters.Add("@HocLuc", SqlDbType.NVarChar, 20).Value = "Trung Bình";
                }
                else if (a >= 2.5 && a < 3.5)
                {
                    ComUpdate.Parameters.Add("@HocLuc", SqlDbType.NVarChar, 20).Value = "Khá";
                }
                else if (a >= 3.5)
                {
                    ComUpdate.Parameters.Add("@HocLuc", SqlDbType.NVarChar, 20).Value = "Giỏi";
                }

                ComUpdate.Parameters.Add("@HanhKiem", SqlDbType.NVarChar, 20).Value = cbhk.Text;

                if (cbhk.Text == "Yếu" && ((txthl.Text == "Yếu") || (txthl.Text == "Trung Bình") || (txthl.Text == "Khá") || (txthl.Text == "Giỏi")))
                {
                    ComUpdate.Parameters.Add("@XepLoai", SqlDbType.NVarChar, 50).Value = "Yếu";
                }
                else if (cbhk.Text == "Trung Bình" && ((txthl.Text == "Trung Bình") || (txthl.Text == "Khá") || (txthl.Text == "Giỏi")))
                {
                    ComUpdate.Parameters.Add("@XepLoai", SqlDbType.NVarChar, 50).Value = "Trung Bình";
                }
                else if (cbhk.Text == "Khá" && ((txthl.Text == "Khá") || (txthl.Text == "Giỏi")))
                {
                    ComUpdate.Parameters.Add("@XepLoai", SqlDbType.NVarChar, 50).Value = "Khá";
                }
                else if (cbhk.Text == "Tốt" && txthl.Text == "Giỏi")
                {
                    ComUpdate.Parameters.Add("@XepLoai", SqlDbType.NVarChar, 50).Value = "Giỏi";
                }
                if (txthl.Text == "Yếu" && ((cbhk.Text == "Trung Bình") || (cbhk.Text == "Khá") || (cbhk.Text == "Tốt")))
                {
                    ComUpdate.Parameters.Add("@XepLoai", SqlDbType.NVarChar, 50).Value = "Yếu";
                }
                else if (txthl.Text == "Trung Bình" && ((cbhk.Text == "Khá") || (cbhk.Text == "Tốt")))
                {
                    ComUpdate.Parameters.Add("@XepLoai", SqlDbType.NVarChar, 50).Value = "Trung Bình";
                }
                else if (txthl.Text == "Khá" && ((cbhk.Text == "Tốt")))
                {
                    ComUpdate.Parameters.Add("@XepLoai", SqlDbType.NVarChar, 50).Value = "Khá";
                }


                Da.UpdateCommand = ComUpdate;
                Da.Update(Dt);
                MessageBox.Show("Bạn Đã Sửa Thành Công!", "Thông Báo!", MessageBoxButtons.OK);

                SqlCommand Com = new SqlCommand();
                Com.Connection = Con;
                Com.CommandType = CommandType.Text;
                Com.CommandType = CommandType.Text;
                Com.CommandText = @"drop table Phu";
                Da.SelectCommand = Com;
                Da.Fill(Dt);
                Close();
                Dispose();
                TongKet TK = new TongKet();
                TK.Show();
            }
        }
    }
}
